package com.cz.fra.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cz.fra.dao.EntityNotFoundException;
import com.cz.fra.dao.PersistenceException;
import com.cz.fra.dao.ReportDao;
import com.cz.fra.entity.Filter;
import com.cz.fra.entity.Report;
import com.cz.fra.utils.DBUtil;

public class JdbcReportDao implements ReportDao {

	private DBUtil dbUtil = new DBUtil();
	
	@Override
	public String addReport(Report report, List<String> img) throws PersistenceException,
			EntityNotFoundException {
		//title should be unique for one user.
		//all reports user added into database should be persist in client database firstly.
		//and check if the title is unique or not
		String result = null;
		String add_report_sql = "insert into "+ DBUtil.databaseName +".frs_report (title, created_by, created_date, updated_by, updated_date,description,locations,baits,species,imgs, commentNo)"
								+"values('"+report.getTitle()+"','"+report.getCreatedBy()+"', CURRENT_TIMESTAMP,'"+report.getUpdatedBy()+"',CURRENT_TIMESTAMP"
								+",'"+report.getDescription()+"','"+report.getLocations()+"','"+report.getBaits()+"','"+report.getSpecies()+"','"+report.getImgs()+"', '"+report.getCommentNo()+"')";
		System.out.println("execute add_report_sql : " + add_report_sql);
		dbUtil.getConnection();
		if(dbUtil.executeUpdate(add_report_sql)){
			result += "report added.";
			//retrieve the newly added report.
			Report newlyAddedReport = getReportByTitleAndUser(report.getTitle(), report.getCreatedBy());
			
//			for(String l : location){
//				String add_report_location = "insert "+ DBUtil.databaseName +".frs_report_LOCATION_JOINT (report_id, location) values ('"+newlyAddedReport.getReportID()+"','"+l+"')";
//				if(dbUtil.executeUpdate(add_report_location)){
//					result += "location added.";
//				}else{
//					result += "location added failed.";
//				}
//			}
//			
//			for(String b : bait){
//				String add_report_bait = "insert "+ DBUtil.databaseName +".frs_report_BAIT_JOINT (report_id, bait) values ('"+newlyAddedReport.getReportID()+"','"+b+"')";
//				if(dbUtil.executeUpdate(add_report_bait)){
//					result += "bait added.";
//				}else{
//					result += "bait added failed.";
//				}
//			}
//			
//			for(String s : species){
//				String add_report_species = "insert "+ DBUtil.databaseName +".frs_report_SPECIES_JOINT (report_id, species) values ('"+newlyAddedReport.getReportID()+"','"+s+"')";
//				if(dbUtil.executeUpdate(add_report_species)){
//					result += "species added.";
//				}else{
//					result += "species added failed.";
//				}
//			}
			
			for(String i : img){
				String add_report_imgs = "insert into "+ DBUtil.databaseName +".frs_img (img_path, report_id, user_id, uploaded_date) values ('"+i+"','"+newlyAddedReport.getReportID()+"','"+newlyAddedReport.getCreatedBy()+"',CURRENT_TIMESTAMP)";
				dbUtil.getConnection();
				if(dbUtil.executeUpdate(add_report_imgs)){
					result += "imgs added.";
				}else{
					result += "imgs added failed.";
				}
			}
		}else {
			result += "report added failed.";	
		}
		return result;
	}

	@Override
	public List<Report> getReportList() throws PersistenceException,
			EntityNotFoundException {
		//Join Location table and Image table to query, one report id related to one record only.
		String get_reports_list_sql = "select * from "+ DBUtil.databaseName +".frs_report ";
		System.out.println("execute get_reports_list_sql : " + get_reports_list_sql);
		ResultSet rs = null;
		List<Report> reportList = new ArrayList<Report>();
		Report report = null;
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(get_reports_list_sql);
			while(rs.next()){
				report = mapRow(rs);
				reportList.add(report);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return reportList;
	}

	@Override
	public List<Report> getReportListByFilter(Filter filter)
			throws PersistenceException, EntityNotFoundException {
		List<Report> reportList = new ArrayList<Report>();
		StringBuffer get_report_by_filter = new StringBuffer("select * from "+ DBUtil.databaseName +".frs_report where ");
		if(filter.getTitle() != null){
			get_report_by_filter.append("title='"+filter.getTitle()+"' AND ");
		}
		if(filter.getUsername() != null){
			get_report_by_filter.append("created_by='"+filter.getUserId()+"' AND ");
		}
		if(filter.getCreatedDate() != null){
			get_report_by_filter.append("created_date>'"+filter.getCreatedDate().toString().substring(0, 19)+"'");
		}
		dbUtil.getConnection();
		ResultSet rs = null;
		Report report = null;
		try {
			rs = dbUtil.executeQuery(get_report_by_filter.toString());
			System.out.println(get_report_by_filter.toString());
			while(rs.next()){
				report = mapRow(rs);
				reportList.add(report);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return reportList;
	}

	public Report getReportByTitleAndUser(String title, int userId) throws PersistenceException, 
			EntityNotFoundException {
		String get_report_by_title_user = "select * from "+ DBUtil.databaseName +".frs_report where title = '"+title+"' and created_by = '"+userId+"'";
		ResultSet rs = null;
		Report report = null;
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(get_report_by_title_user);
			if(rs.next()){
				report = mapRow(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return report;
	}
	
	public Report mapRow(ResultSet rs) throws SQLException{
		Report report = new Report();
		
		report.setReportID(rs.getInt("report_id"));
		report.setTitle(rs.getString("title"));
		report.setDescription(rs.getString("description"));
		report.setLocations(rs.getString("locations"));
		report.setBaits(rs.getString("baits"));
		report.setSpecies(rs.getString("species"));
		report.setImgs(rs.getString("imgs"));
		report.setCreatedBy(rs.getInt("created_by"));
		report.setCreatedDate(rs.getTimestamp("created_date"));
		report.setUpdatedBy(rs.getInt("updated_by"));
		report.setUpdatedDate(rs.getTimestamp("updated_date"));
		report.setCommentNo(rs.getInt("commentNo"));
		
		return report;
	}

	@Override
	public Report getReportByID(int reportId) throws PersistenceException,
			EntityNotFoundException {
		String get_report_by_id = "select * from "+ DBUtil.databaseName +".frs_report where report_id = '"+reportId+"'";
		ResultSet rs = null;
		Report report = null;
		dbUtil.getConnection();
		try {
			rs = dbUtil.executeQuery(get_report_by_id);
			if(rs.next()){
				report = mapRow(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			dbUtil.closeConnection();
		}
		return report;
	}

	@Override
	public void incrementCommentNum(int reportId) throws PersistenceException,
			EntityNotFoundException {
		String reset_comment_number = "update "+ DBUtil.databaseName +".frs_report r set r.commentNo = (select COUNT(*) from "+ DBUtil.databaseName +".FRS_COMMENT where report_id = '"+reportId+"') where r.report_id = '"+reportId+"'";
		dbUtil.getConnection();
		if(dbUtil.executeUpdate(reset_comment_number)){
			System.out.println("reset comment number success");
		}else{
			System.out.println("reset comment number failed");
		}
		
	}
}
